﻿using System;
using System.Collections.Generic;
using BalanceManagerApp.Model;

namespace BalanceManagerApp.DataAccess
{
    public class FlowFundsRepository : IFlowFundsRepository
    {
        public List<FlowFundsItem> SelectItems()
        {
            var query = @"
SELECT FlowFunds.Id, FlowFunds.IncomeAmount, FlowFunds.ExpenseAmount, FlowFunds.Date, Item.Id AS ItemId, Item.Name AS ItemName, Category.Name AS ItemCategory
FROM Item INNER JOIN
FlowFunds ON Item.Id = FlowFunds.Item INNER JOIN
 Category ON Item.Category = Category.Id";

            List<FlowFundsItem> flowFundsList = new List<FlowFundsItem>();
            using (var queryHelper = new SqlCeDbQueryHelper())
            using (var reader = queryHelper.ExecuteReader(query, null))
            {
                while (reader.Read())
                {
                    var item = new Item
                    {
                        Id = reader.GetInt32(4),
                        Name = reader.GetString(5),
                        Category = reader.GetString(6)
                    };
                    var flowFundsItem = new FlowFundsItem
                    {
                        Id = reader.GetInt32(0),
                        Item = item,
                        IncomeAmount = reader.GetDecimal(1),
                        ExpenseAmount = reader.GetDecimal(2),
                        Date = reader.GetDateTime(3)
                    };

                    flowFundsList.Add(flowFundsItem);
                }

                return flowFundsList;
            }
        }

        public List<FlowFundsItem> SelectItemsForPeriod(DateTime beginDate, DateTime endDate)
        {
            var query = @"
SELECT SUM(FlowFunds.IncomeAmount) AS IncomeAmount, SUM(FlowFunds.ExpenseAmount) AS ExpenseAmount, Item.Name AS ItemName
FROM Item INNER JOIN
FlowFunds ON Item.Id = FlowFunds.Item INNER JOIN
 Category ON Item.Category = Category.Id
WHERE FlowFunds.Date BETWEEN @beginDate AND @endDate
GROUP BY Item.Name";

            var flowFundsList = new List<FlowFundsItem>();
            var parameters = new Dictionary<string, object>();
            parameters.Add("@beginDate", beginDate);
            parameters.Add("@endDate", endDate);
            using (var queryHelper = new SqlCeDbQueryHelper())
            using (var reader = queryHelper.ExecuteReader(query, parameters))
            {
                while (reader.Read())
                {
                    var item = new Item
                    {
                        Name = reader.GetString(2)
                    };
                    var flowFundsItem = new FlowFundsItem
                    {
                        Item = item,
                        IncomeAmount = reader.GetDecimal(0),
                        ExpenseAmount = reader.GetDecimal(1),
                    };

                    flowFundsList.Add(flowFundsItem);
                }

                return flowFundsList;
            }
        }

        public void AddFlowFundsItem(Item item, decimal incomeAmount, decimal expenseAmount, DateTime date)
        {
            var query = @"INSERT FlowFunds(Item, IncomeAmount, ExpenseAmount, Date) VALUES (@item, @incomeAmount, @expenseAmount, @date)";
            var parameters = new Dictionary<string, object>();
            parameters.Add("@item", (object)item.Id);
            parameters.Add("@incomeAmount", (object)incomeAmount);
            parameters.Add("@expenseAmount", (object)expenseAmount);
            parameters.Add("@date", (object)date);

            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                queryHelper.ExecuteNonQuery(query, parameters);
            }
        }
    }
}
